package DTO;

import global.global;

import java.util.ArrayList;

import database.DatabaseHelper;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;

public class TrafficLawDTO {
	private String ID;
	private String chapter;
	private String chapterName;
	private String article;
	private String articleName;
	private String contents;
	
	//Property ID
	public String getId() { return ID; }
	public void setId(String ID) { this.ID = ID; }
	
	//Property Chapter
	public String getChapter() { return chapter; }
	public void setChapter(String chapter) { this.chapter = chapter; }
	
	//Property Chapter Name
	public String getChapterName() { return chapterName; }
	public void setChapterName(String chapterName) { this.chapterName = chapterName; }
	
	//Property Article
	public String getArticle() { return article; }
	public void setArticle(String article) { this.article = article; }
	
	//Property Article Name
	public String getArticleName() { return articleName; }
	public void setArticleName(String articleName) { this.articleName = articleName; }
	
	//Property Contents
	public String getContents() { return contents; }
	public void setContents(String contents) { this.contents = contents; }
	
	public static ArrayList<TrafficLawDTO> getByKeyWord(Context context, String keyWord, String chapter){
		ArrayList<TrafficLawDTO> result = new ArrayList<TrafficLawDTO>();
		
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
		
		String query = null;
		if(keyWord == null){
			if(chapter == null){
				//query = "SELECT * FROM TrafficLaws WHERE ";
			}
			else{
				query = "SELECT TrafficLaws.ID AS TrafficLawsID" +
						", TrafficLaws.Chapter AS TrafficLawsChapter" +
						", TrafficLaws.Article AS TrafficLawsArticle" +
						", TrafficLaws.Content AS TrafficLawsContent" +
						", Chapters.Name AS ChaptersName" +
						", Articles.Name AS ArticlesName " +
						"FROM TrafficLaws LEFT JOIN Chapters ON TrafficLaws.Chapter = Chapters.Chapter " +
						   "LEFT JOIN Articles ON TrafficLaws.Article = Articles.Article WHERE Chapter = '" + chapter + "'";
			}
		}else{
			keyWord = global.removeAccent(keyWord);	
			keyWord = keyWord.trim();
			if(chapter == null){
				query = "SELECT TrafficLaws.ID AS TrafficLawsID" +
						", TrafficLaws.Chapter AS TrafficLawsChapter" +
						", TrafficLaws.Article AS TrafficLawsArticle" +
						", TrafficLaws.Content AS TrafficLawsContent" +
						", Chapters.Name AS ChaptersName" +
						", Articles.Name AS ArticlesName " +
						"FROM TrafficLaws LEFT JOIN Chapters ON TrafficLaws.Chapter = Chapters.Chapter " +
						   "LEFT JOIN Articles ON TrafficLaws.Article = Articles.Article " +
						   		"WHERE Articles.UnsignName like '%" + keyWord + "%' " +
						   		"OR UnsignContent like '%" + keyWord + "%' " +
						   		"OR Chapters.UnsignName like '%" + keyWord + "%' ";
			}else{
				query = "SELECT TrafficLaws.ID AS TrafficLawsID" +
						", TrafficLaws.Chapter AS TrafficLawsChapter" +
						", TrafficLaws.Article AS TrafficLawsArticle" +
						", TrafficLaws.Content AS TrafficLawsContent" +
						", Chapters.Name AS ChaptersName" +
						", Articles.Name AS ArticlesName " +
						"FROM TrafficLaws LEFT JOIN Chapters ON TrafficLaws.Chapter = Chapters.Chapter " +
						   "LEFT JOIN Articles ON TrafficLaws.Article = Articles.Article " +
						   		"WHERE (Articles.UnsignName like '%" + keyWord + "%' " +
						   		"OR UnsignContent like '%" + keyWord + "%' " +
						   		"OR Chapters.UnsignName like '%" + keyWord + "%') " +
								"AND TrafficLaws.Chapter = '" + chapter + "'";
			}
			
		}
		Cursor cursor = null;
	 	try{
	 		cursor = myDbHelper.getMyDataBase().rawQuery(query, null);
	 	}catch(Exception e){
	 		
	 	}
		if(cursor.moveToFirst()){
			do{
				TrafficLawDTO trafficLawDTO = new TrafficLawDTO();
				trafficLawDTO.setId(cursor.getString(cursor.getColumnIndex("TrafficLawsID")));
				trafficLawDTO.setChapter(cursor.getString(cursor.getColumnIndex("TrafficLawsChapter")));
				//ChapterDTO chapterDTO = ChapterDTO.getChapter(context, trafficLawDTO.getChapter());
				trafficLawDTO.setChapterName(cursor.getString(cursor.getColumnIndex("ChaptersName")));
				trafficLawDTO.setArticle(cursor.getString(cursor.getColumnIndex("TrafficLawsArticle")));
				//ArticleDTO articleDTO = ArticleDTO.getArticle(context, trafficLawDTO.getArticle());
				trafficLawDTO.setArticleName(cursor.getString(cursor.getColumnIndex("ArticlesName")));
				trafficLawDTO.setContents(cursor.getString(cursor.getColumnIndex("TrafficLawsContent")));
				result.add(trafficLawDTO);
			}while(cursor.moveToNext());
		}
		myDbHelper.close();
		return result;
	}
}
